/*
 * Powered By [rapid-framework]
 * Web Site: http://www.rapid-framework.org.cn
 * Google Code: http://code.google.com/p/rapid-framework/
 * Since 2008 - 2016
 */

package com.jzwl.xydk.manager.user.userBasicinfo.dao;

import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.HashMap;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.jzwl.common.id.Sequence;
import com.jzwl.common.page.PageObject;
import com.jzwl.system.base.dao.BaseDAO;
import com.jzwl.xydk.manager.user.userBasicinfo.pojo.UserBasicinfo;
import com.jzwl.xydk.wap.business.home.pojo.UserSkillclassF;



@Repository("userBasicinfoDao")
public class UserBasicinfoDao {
	
	@Autowired
	private BaseDAO baseDAO;//dao基类，操作数据库
	
	
	public boolean addUserBasicinfo(Map<String, Object> map) {
		Date date = new Date();
		//自动注入时间戳为ID 酌情修改数据库类型为bigint  int会越界
		map.put("id",  Sequence.nextId());
		map.put("openId",  "0");
		map.put("auditState",0);
		map.put("createDate",date);
		map.put("isRecommend",0);//是否推荐
		map.put("skillStatus",0);
		map.put("isDelete",0);
		//map.put("viewNum",0);
		
		//添加用户临时存储的图片和昵称
		addUserTemporary(map);
		String sql = "insert into `xiaoka-xydk`.user_basicinfo " 
				 + " (id,userName,cityId,schoolId,phoneNumber,email,wxNumber,openId,aboutMe,"
				 + "checkType,nickname,headPortrait,auditState,viewNum,createDate,createUser,isRecommend,skillStatus,isDelete) " 
				 + " values "
				 + " (:id,:userName,:cityId,:schoolId,:phoneNumber,:email,:wxNumber,:openId,:aboutMe,"
				 + ":checkType,:nickname,:picUrl,:auditState,:viewNum,:createDate,:createUser,:isRecommend,:skillStatus,:isDelete)";
		return baseDAO.executeNamedCommand(sql, map);
	}
	
	
	public boolean addUserTemporary(Map<String, Object> map) {
		Date date = new Date();
		//自动注入时间戳为ID 酌情修改数据库类型为bigint  int会越界
		map.put("id",  Sequence.nextId());
		map.put("userId",  map.get("id"));
		
		String sql = "insert into `xiaoka-xydk`.user_temporary " 
				 + " (id,userId,nickname,picUrl) " 
				 + " values "
				 + " (:id,:userId,:nickname,:picUrl)";
		
		return baseDAO.executeNamedCommand(sql, map);
	}
	
	
	
	public String getColumns() {
		return ""
				+" t.id as id,"
				+" t.userName as userName,"
				+" t.cityId as cityId,"
				+" c.cityName as cityName,"
				+" t.schoolId as schoolId,"
				+" si.schoolName as schoolName,"
				+" t.phoneNumber as phoneNumber,"
				+" t.email as email,"
				+" t.wxNumber as wxNumber,"
				+" t.openId as openId,"
				+" t.aboutMe as aboutMe,"
				+" t.auditState as auditState,"
				+" t.auditDate as auditDate,"
				+" t.auditUser as auditUser,"
				+" t.auditNotes as auditNotes,"
				+" t.viewNum as viewNum,"
				+" t.createDate as createDate,"
				+" t.createUser as createUser,"
				+" t.isRecommend as isRecommend,"
				+" t.skillStatus as skillStatus,"
				+" t.isDelete as isDelete"
				;
	}
	

	public PageObject queryUserBasicinfoList(Map<String,Object> map) {
		// [column]为字符串拼接, {column}为使用占位符. 如username='[username]',偷懒时可以使用字符串拼接 
		// [column] 为PageRequest的属性
		
		String sql="select " + getColumns() + " from `xiaoka-xydk`.user_basicinfo t "
				+" LEFT JOIN `xiaoka`.tg_city c ON t.cityId = c.id "
				+" LEFT JOIN `xiaoka`.tg_school_info si ON si.id = t.schoolId "
				+" where t.openId='0' and t.isDelete = 0 ";
		
		  	if(null !=map.get("userName") && StringUtils.isNotEmpty(map.get("userName").toString())){
			  		sql=sql+ " and t.userName  like '%" + map.get("userName") +"%'";
		  	}
		  	if(null !=map.get("cityId") && StringUtils.isNotEmpty(map.get("cityId").toString())){
			  		sql=sql+ " and t.cityId  = " + map.get("cityId") +"";
		  	}
		  	if(null !=map.get("schoolId") && StringUtils.isNotEmpty(map.get("schoolId").toString())){
			  		sql=sql+ " and t.schoolId  = " + map.get("schoolId") +"";
		  	}
		  	if(null !=map.get("phoneNumber") && StringUtils.isNotEmpty(map.get("phoneNumber").toString())){
			  		sql=sql+ " and t.phoneNumber  = " + map.get("phoneNumber") +"";
		  	}
		  	if(null !=map.get("email") && StringUtils.isNotEmpty(map.get("email").toString())){
			  		sql=sql+ " and t.email  = " + map.get("email") +"";
		  	}
		  	if(null !=map.get("wxNumber") && StringUtils.isNotEmpty(map.get("wxNumber").toString())){
			  		sql=sql+ " and t.wxNumber  = " + map.get("wxNumber") +"";
		  	}
		  	if(null !=map.get("openId") && StringUtils.isNotEmpty(map.get("openId").toString())){
			  		sql=sql+ " and t.openId  = " + map.get("openId") +"";
		  	}
		  	if(null !=map.get("aboutMe") && StringUtils.isNotEmpty(map.get("aboutMe").toString())){
			  		sql=sql+ " and t.aboutMe  = " + map.get("aboutMe") +"";
		  	}
		  	if(null !=map.get("auditState") && StringUtils.isNotEmpty(map.get("auditState").toString())){
			  		sql=sql+ " and t.auditState  = " + map.get("auditState") +"";
		  	}
		  	if(null !=map.get("auditDate") && StringUtils.isNotEmpty(map.get("auditDate").toString())){
			  		sql=sql+ " and t.auditDate >= " + map.get("auditDateBegin") +"";
			  		sql=sql+ " and t.auditDate <= " + map.get("auditDateEnd"  ) +"";
		  	}
		  	if(null !=map.get("auditUser") && StringUtils.isNotEmpty(map.get("auditUser").toString())){
			  		sql=sql+ " and t.auditUser  = " + map.get("auditUser") +"";
		  	}
		  	if(null !=map.get("auditNotes") && StringUtils.isNotEmpty(map.get("auditNotes").toString())){
			  		sql=sql+ " and t.auditNotes  = " + map.get("auditNotes") +"";
		  	}
		  	if(null !=map.get("viewNum") && StringUtils.isNotEmpty(map.get("viewNum").toString())){
			  		sql=sql+ " and t.viewNum  = " + map.get("viewNum") +"";
		  	}
		  	if(null !=map.get("createDate") && StringUtils.isNotEmpty(map.get("createDate").toString())){
			  		sql=sql+ " and t.createDate >= " + map.get("createDateBegin") +"";
			  		sql=sql+ " and t.createDate <= " + map.get("createDateEnd"  ) +"";
		  	}
		  	if(null !=map.get("createUser") && StringUtils.isNotEmpty(map.get("createUser").toString())){
			  		sql=sql+ " and t.createUser  = " + map.get("createUser") +"";
		  	}
		  	if(null !=map.get("isRecommend") && StringUtils.isNotEmpty(map.get("isRecommend").toString())){
			  		sql=sql+ " and t.isRecommend  = " + map.get("isRecommend") +"";
		  	}
		  	if(null !=map.get("skillStatus") && StringUtils.isNotEmpty(map.get("skillStatus").toString())){
			  		sql=sql+ " and t.skillStatus  = " + map.get("skillStatus") +"";
		  	}
		  	if(null !=map.get("isDelete") && StringUtils.isNotEmpty(map.get("isDelete").toString())){
			  		sql=sql+ " and t.isDelete  = " + map.get("isDelete") +"";
		  	}
					
					
		sql=sql+ " order by t.id desc";
		
		PageObject po = baseDAO.queryForMPageList(sql, new Object[]{},map);
		
		return po;
	}
	
	public boolean updateUserTemporary(Map<String, Object> map) {
		
		String sql ="update `xiaoka-xydk`.user_temporary set "
				+ " nickname=:nickname,picUrl=:picUrl "
				+ " where userId=:id";
		
		return  baseDAO.executeNamedCommand(sql, map);
	}

	public boolean updateUserBasicinfo(Map<String, Object> map) {
		
		String sql ="update `xiaoka-xydk`.user_basicinfo set "
				+ " userName=:userName,nickname=:nickname,cityId=:cityId,schoolId=:schoolId,phoneNumber=:phoneNumber,email=:email,wxNumber=:wxNumber,aboutMe=:aboutMe,checkType=:checkType,viewNum=:viewNum "
				+ " where id=:id";
		
		return  baseDAO.executeNamedCommand(sql, map);
	}

	public boolean deleteUserBasicinfo(Map<String, Object> map) {
		
		String sql ="update `xiaoka-xydk`.user_basicinfo set "
				+ " isDelete = 1 "
				+ " where id=:id";
		
		return  baseDAO.executeNamedCommand(sql, map);
	}
	
	
	@SuppressWarnings("unchecked")
	public Map<String,Object> getById(Map<String, Object> map) {
		
		Map<String,Object> resMap = new HashMap<String,Object>();

		String sql = "select " + getColumns() + ",t.viewNum,t.checkType,t.nickname as nickname,ut.picUrl as picUrl from `xiaoka-xydk`.user_basicinfo t "
				+" left join `xiaoka-xydk`.user_temporary ut on t.id = ut.userId "
				+" LEFT JOIN `xiaoka`.tg_city c ON t.cityId = c.id "
				+" LEFT JOIN `xiaoka`.tg_school_info si ON si.id = t.schoolId "
				+ " where t.id = "+ map.get("id") + "";
		
		resMap = baseDAO.queryForMap(sql);
		
		return resMap;
	
	}
	
	@SuppressWarnings("unchecked")
	public Map<String,Object> getProvence(String cityId) {
		Map<String,Object> resMap = new HashMap<String,Object>();
		String sql = "SELECT id,cityName,province,number,cityOperateManager,phone,longitude,latitude,remark "
					+ "FROM tg_city WHERE isDelete=0 and id="+cityId+" AND province IS NOT NULL  GROUP BY   province ";
		resMap = baseDAO.queryForMap(sql);
		return resMap;
	}
	
	//查询所属学校
	@SuppressWarnings("unchecked")
	public Map<String,Object> getSholName(String sholId) {
		Map<String,Object> resMap = new HashMap<String,Object>();
		String sql = "SELECT * FROM tg_school_info shol WHERE id="+sholId+"";
		resMap = baseDAO.queryForMap(sql);
		return resMap;
	}
	/**
	 * @param map
	 * @return
	 */
	public List querySchoolInfoByCityId(Map<String, Object> map) {
		String sql = "select *,t.id as schoolId from xiaoka.tg_school_info t left join xiaoka.tg_city tc on t.cityId = tc.id where t.cityId='"+map.get("cityId")+"'";
		return baseDAO.queryForList(sql);
	}
	/**
	 * 根据省查市
	 * @param map
	 * @return list
	 * dxf
	 */
	public List queryCityByPro(String provence) {
		String sql = "SELECT id as value,cityName as text " + " FROM tg_city  WHERE isDelete=0 and province='"
				+ provence + "'";
		return baseDAO.queryForList(sql);
	}
	
	/**
	 * 获取城市的基本信息从校咖（xiaoka）中获取
	 * 
	 * @param map
	 * @return
	 */
	public List<Map<String, Object>> getCityInfo() {
		
		String sql = "select * from xiaoka.tg_city where isDelete = 0";
		
		return baseDAO.queryForList(sql);
		
	}
	
	
	

}
